package gu.sql2java.wherehelper;

import static com.google.common.base.Preconditions.checkNotNull;
import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Strings.isNullOrEmpty;
import static gu.sql2java.wherehelper.WhereHelpers.isCamelcase;
import static gu.sql2java.wherehelper.WhereHelpers.isSnakelcase;
import static gu.sql2java.wherehelper.WhereHelpers.toCamelcase;
import static gu.sql2java.wherehelper.WhereHelpers.toSnakecase;
import static gu.sql2java.transformer.ColumnTransformer.asUnsignedLong;
import static gu.sql2java.utils.DateSupport.getDateFromString;
import static gu.sql2java.utils.DateSupport.TIMESTAMP_FORMATTER_STR;
import static com.google.common.base.MoreObjects.firstNonNull;
import static gu.sql2java.wherehelper.BeanShellWhereBuilder.getFormatJsonFieldFunction;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.parser.ParserConfig;
import com.google.common.base.Function;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.LoadingCache;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Sets;
import com.google.common.primitives.Primitives;

import bsh.EvalError;
import bsh.Interpreter;
import gu.sql2java.BaseRow;
import gu.sql2java.RowMetaData;
import gu.sql2java.SimpleLog;
import gu.sql2java.bean.BeanPropertyUtils;

/**
 * 基于 BeanShell 脚本引擎实现动态生成SQL where 语句<br> 
 * 
 * @author guyadong
 *
 */
public class WhereHelper {
    /** 内置变量名:指定 ORDER BY 排序的字段名 */
    public static final String VAR_ORDER_BY_COLUMN = "order_by_column";
    /** 内置变量名:指定 GROUP BY 分组查询指定的字段名 */
    public static final String VAR_GROUP_BY_COLUMN = "group_by_column";
    /** 内置变量名:指定分页查询 row_count 参数 */
    public static final String VAR_LIMIT_ROW_COUNT = "limit_row_count";
    /** 内置变量名:指定分页查询 offset 参数 */
    public static final String VAR_LIMIT_OFFSET = "limit_offset";
    /** BeanShell 内置变量,不可被修改 */
    private static final String KEYWORD_BSH = "bsh";
    private static final Set<String> KEYWORDS = ImmutableSet.<String>builder()
    		.add(KEYWORD_BSH)
    		.add(BeanShellWhereBuilder.KEYWORD_COND_COUNT)
    		.add(BeanShellWhereBuilder.KEYWORD_WHERE_BUFFER)
    		.add(BeanShellWhereBuilder.KEYWORD_EXP_BUFFER)
    		.build();
    /**
     * 内置变量名集合
     */
    private static final HashSet<String> BUILTIN_VARS = Sets.newHashSet(
    		VAR_LIMIT_ROW_COUNT,
    		VAR_LIMIT_OFFSET
    		);
    private Interpreter interpreter = new Interpreter();
	private String timeFormatter = TIMESTAMP_FORMATTER_STR;
	PropertyUtilsBean propertyUtils = BeanUtilsBean.getInstance().getPropertyUtils();
	/**
	 * BeanShell脚本
	 */
	private final String bshScript;
	/**
	 * 是否输出调试日志
	 */
	private boolean debuglog = false;
	private Set<String> referenceVariables = Collections.emptySet();
	private Class<? extends BaseRow> targetClass = BaseRow.class;
	/**
	 * 变量字段名对应的类型映射
	 */
	private Map<String, Class<?>> varTypes = Collections.emptyMap();
	private String defaultOrderByColumns;
	private String defaultGroupByColumns;
	private String orderByVar;
	private String groupByVar;
	/**
	 * 构造方法
	 * @param bshScript BeanShell 脚本
	 */
	public WhereHelper(String bshScript) {
		this.bshScript = checkNotNull(bshScript,"bshScript is null");
		// 将当前对象添加到namespace,这样脚本中才可以访问对象中的方法,isEmpty,op
		interpreter.getNameSpace().importObject(this);
	}
	/**
	 * 构造方法
	 * @param builder
	 */
	WhereHelper(BeanShellWhereBuilder builder) {
		this(checkNotNull(builder,"bshScript is null").buildScript());
		this.referenceVariables = builder.getReferenceVariables();
		this.targetClass  = builder.getTargetClass();
		this.varTypes = builder.getVarTypes();
		this.debuglog = builder.debuglog();
		this.defaultOrderByColumns=builder.getOrderByColumns();
		this.defaultGroupByColumns=builder.getGroupByColumns();
		this.orderByVar = builder.getOrderByVarname();
		this.groupByVar = builder.getGroupByVarname();
	}
	
	/**
	 * 设置日期对象的格式，默认为{@link gu.sql2java.utils.DateSupport#TIMESTAMP_FORMATTER_STR}
	 * @param timeFormatter
	 * @return 当前对象
	 */
	public WhereHelper timeFormatter(String timeFormatter) {
		if(!isNullOrEmpty(timeFormatter)){
			this.timeFormatter = timeFormatter;
		}
		return this;
	}
	/**
	 * 定义脚本执行变量,在{@link #with(Object)}方法之后调用有效
	 * @param varname 变量名,为空或{@code null}忽略
	 * @param value 变量的值
	 * @return 当前对象
	 */
	public WhereHelper defineVariable(String varname,Object value) {
		return defineVariable(varname,value,null);
	} 
	/**
	 * 定义脚本执行变量,在{@link #with(Object)}方法之后调用有效
	 * @param varname 变量名,为空或{@code null}忽略
	 * @param value 变量的值
	 * @param expectType 希望的数据类型,为{@code null}忽略，如果不为{@code null},对于String类型的输入尝试解析为希望类型的对象或数组
	 * @return 当前对象
	 */
	public WhereHelper defineVariable(String varname,Object value,Class<?> expectType) {
	    if(!isNullOrEmpty(varname)){
	        try {
	            value = guess(value, expectType);
	            interpreter.set(varname, escape(value));
	            SimpleLog.log(debuglog,"{} = {}",varname,value);
	        } catch (EvalError e) {
	            throw new RuntimeException(e);
	        }
	    }
	    return this;
	} 
	/**
	 * 根据输入的参数对象提供的SQL查询要求的字段参数定义脚本执行变量<br>
	 * SQL查询字段参数可以封装在Java Bean或Map对象，不可为{@code null}<br>
	 * @param params
	 * @return 当前对象
	 */
	public WhereHelper with(Object params){
		initVars(checkNotNull(params,"params is null"));
		return this;
	}
	/**
	 * 从{@code valueSupplier}中获取WhereHelper所有引用变量的值定义定义
	 * 到WhereHelper的BeanShell脚本执行空间,
	 * 自动匹配变量命名格式
	 * @param valueSupplier 字段变量值提供对象,为{@code null}忽略
	 * @return 当前对象
	 */
	public WhereHelper with(Function<String,String>valueSupplier){
		if(null != valueSupplier){			
			referenceVariables.forEach(varname->{
				String v = valueSupplier.apply(varname);
				if(null == v){
					// 自动匹配变量命名格式
					if(isCamelcase(varname)){
						v = valueSupplier.apply(toSnakecase(varname));
					}else if (isSnakelcase(varname)) {
						v = valueSupplier.apply(toCamelcase(varname));
					}
				}
				if(null == v){
					defineVariable(varname, v);        				
				}else {
					Class<?> varType = variableTypeOf(varname);
					if(null == varType){
						defineVariable(varname, v);							
					}else if(String.class == varType){
						defineVariable(varname, v);
					}else {
						/**
						 * 如果获取到变量的类型则执行转换
						 */
						defineVariable(varname, JSON.parseObject(v, varType,ParserConfig.global,null,0));    
					}
				}
			});
		}
		return this;
	}
	/**
	 * 根据{@link #with(Object)}提供的SQL查询要求的字段参数执行BeanShell脚本创建SQL Where语句<br>
	 * WhereHelper执行{@link BeanShellWhereBuilder}生成的BeanShell脚本，脚本根据{@link #with(Object)}提供的参数，生成SQL WHERE语句
	 * @return 动态生成的SQL语句
	 */
	public String where(){
		try {
			beforeWhere();
			StringBuffer buffer = new StringBuffer();
			// 为bsh脚本设置字符串输出缓冲区
			interpreter.set("where_buffer", buffer);
			interpreter.eval(bshScript);
			unsetVars();
			String where = buffer.toString();
			SimpleLog.log(debuglog,"{}",where);
			return where;
		} catch (EvalError e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * BeanShell运行环境调用的方法:判断一个对象是否为null或空,参见{@link BeanPropertyUtils#isEmpty(Object)}
	 * @param value
	 */
	public boolean isEmpty(Object value) {
		return BeanPropertyUtils.isEmpty(value);
	}
	
	/**
	 * BeanShell运行环境调用的方法:判断一个对象是否为true<br>
	 * 类型为Boolean,Number,String都可以,
	 * 当为Number类型时,转为整数不为0即判定为true,
	 * 当为String类型时 true|on|1|y(es)? 都判定为true,
	 * 为{@code null}不判定为true
	 * @param value
	 */
	public boolean isTrue(Object value){
		if(value instanceof Boolean){
			return (Boolean)value;
		}else	if(value instanceof Number){
			return 0 !=((Number)value).intValue() ;
		}
		return null != value && value.toString().toLowerCase().matches("true|on|1|y(es)?");
	}
	/**
	 * BeanShell运行环境调用的方法:判断一个对象是否为false
	 * 类型为Boolean,Number,String都可以,
	 * 当为Number类型时,转为整数为0即判定为false,
	 * 当为String类型时 false|off|0|no? 都判定为false,
	 * 为{@code null}不判定为true
	 * @param value
	 */
	public boolean isFalse(Object value){
		if(value instanceof Boolean){
			return !(Boolean)value;
		}else	if(value instanceof Number){
			return 0 ==((Number)value).intValue() ;
		}
		return null != value && value.toString().toLowerCase().matches("false|off|0|no?");
	}
	/**
	 * BeanShell运行环境调用的方法:输入参数为null返回{@code true}
	 * @param value
	 * @since 3.25.0
	 */
	public boolean isNull(Object value) {
		return null == value;
	}
	/**
	 * BeanShell运行环境调用的方法:输入参数不为null返回{@code true}
	 * @param value
	 * @since 3.25.0
	 */
	public boolean isNonull(Object value) {
		return null != value;
	}
	/**
	 * BeanShell运行环境调用的方法:根据指定的字段名{@code field}，计算比较表达式<br>
	 * 当{@code field}对应的值为普通数据类型时,返回表达式 {@code field = value}<br>
	 * 当{@code field}对应的值为数组,集合类型时,返回表达式 {@code field in (v1,v2,v3)},
	 * @param field
	 */
	public String op(String field,boolean not) {
		return op(field,field,not, false);
	}
	/**
	 * BeanShell运行环境调用的方法:根据指定的字段名{@code field}，计算比较表达式<br>
	 * 当{@code field}对应的值为普通数据类型时,返回表达式 {@code field = value}<br>
	 * 当{@code field}对应的值为数组,集合类型时,返回表达式 {@code field in (v1,v2,v3)},
	 * @param left
	 * @param field
	 * @param orNull 为{@code true}输出等价或为NULL表达式，例如 (left=value OR left IS NULL)
	 * @since 3.17.5
	 */
	public String op(String left,String field,boolean not, boolean orNull) {
	    if(!isNullOrEmpty(left) && !isNullOrEmpty(field)) {
	        try {
	        	Object value = interpreter.get(field);
	            String exp= left + " " + asCond(value, not);
	            if(orNull && null!=value) {
	            	exp = String.format("(%s OR %s IS NULL)",exp,left);
	            }
	            return exp;
	        } catch (EvalError e) {
	            e.printStackTrace();
	        }
	    }
	    return null;
	}
	public String op(String field) {
		return op(field,false);
	}
	@SuppressWarnings({ "rawtypes", "unused" })
	private final LoadingCache<Class, Function> AGG_FUN_CACHE = CacheBuilder.<Class, Function>newBuilder()
			.build(new CacheLoader<Class, Function>(){
				@Override
				public Function load(Class key) throws Exception {
					return (Function) key.newInstance();
				}});
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String bitAgg(String field,Class clazz) {
		if(!isNullOrEmpty(field)) {
	        try {
	        	Object value = interpreter.get(field);
	        	if(null != clazz) {
	        		try {
	        			return String.valueOf((AGG_FUN_CACHE.getUnchecked(clazz)).apply(value));						
					} catch (Exception e) {
						e.printStackTrace();
					}
	        	}
	        	Long num = asUnsignedLong(value);
				if (null!=num) {
					value = num;
				}
	            return String.valueOf(value);
	        } catch (EvalError e) {
	            e.printStackTrace();
	        }
	    }
	    return null;
	}
	/**
	 * BeanShell运行环境调用的方法:判断{@code right}指定的值是否等于{@code left}指定的变量 <br>
	 * 如果{@code right},{@code left}相相等则返回{@code true},
	 * 否则判断{@code right}是否为{@link Iterable}类型或数组，
	 * 如果是则判断{@code right}包含{@code left}，包含则返回{@code true}
	 * 否则返回{@code false}
	 * @param left
	 * @param right
	 * @since 3.25.0
	 */
	@SuppressWarnings("rawtypes")
	public boolean eqin(Object left,Object right) {
		if(eq(left,right)) {
			return true;
		}
		if(null != left && !isEmpty(right)) {
			if(right instanceof Iterable) {
				for(Iterator itor=((Iterable)right).iterator();itor.hasNext();) {
					if(eq(left, itor.next())) {
						return true;
					}
				}
			}else if(right.getClass().isArray()) {
				Class<?> componentType = right.getClass().getComponentType();
				if(componentType.isInstance(left)) {
					for(int i=0,end_i=Array.getLength(right);i<end_i;++i) {
						if(left.equals(Array.get(right, i))) {
							return true;
						}
					}
				}else if(left instanceof String){
					if(Number.class.isAssignableFrom(componentType)) {
						try {
							Object _left = JSON.parseObject((String)left,componentType,ParserConfig.global,null,0);
							return eqin(_left,right);
						} catch (JSONException e) {
							// DO NOTHING
						}
					}else if(Date.class.isAssignableFrom(componentType)) {
						AtomicReference<String>pattern = new AtomicReference<>();
						Date _left = getDateFromString((String)left,pattern);
						if(null != _left) {
							SimpleDateFormat fmt = new SimpleDateFormat(pattern.get());
							for(int i=0,end_i=Array.getLength(right);i<end_i;++i) {
								if(left.equals(fmt.format((Date)Array.get(right, i)))) {
									return true;
								}
							}
						}
					}
				}
			}
		}
		return false;
	}
	/**
	 * BeanShell运行环境调用的方法:判断{@code right}指定的值是否等于{@code left}指定的变量 <br>
	 * 如果{@code right},{@code left}相相等则返回{@code true},
	 * 否则判断{@code left}是否为String类型，
	 * 如果是则尝试将{@code left}转换为{@code right}的类型再比较
	 * 否则返回{@code false}
	 * @param left 
	 * @param right
	 * @since 3.25.0
	 */
	public boolean eq(Object left,Object right) {
		if(Objects.equals(left,right)) {
			return true;
		}
		if(null != left && null != right) {
			if(left.getClass().isInstance(right) || left.getClass().isInstance(right)) {
				return false;
			}
			if(left instanceof String){
				if(right instanceof Number) {
					/** 如果 left 为String类型,尝试解析为value的类型 */
					try {
						Object _var = JSON.parseObject((String)left,right.getClass(),ParserConfig.global,null,0);;
						return eq(_var, right);
					} catch (JSONException e) {
						// DO NOTHING
					}
				}else if(right instanceof Date) {
					AtomicReference<String>pattern = new AtomicReference<>();
					Date _left = getDateFromString((String)left,pattern);
					if(null != _left) {
						return left.equals(new SimpleDateFormat(pattern.get()).format((Date)right));
					}
				}
			}
			
		}
		return false;
	}
	public String likeOp(String like_op,String pattern) {
		switch(like_op){
		case "LEFT":
			return "'"+pattern+"%'";
		case "RIGHT":
			return "'%"+pattern+"'";
		default:
			return "'%"+pattern+"%'";
		}
	}
	/**
	 * 如果输入参数(input)为字符串数组或集合则返回以separator为分割符连接的字符串，
	 * 否则将input以转为字符串返回
	 * @param input
	 * @param separator
	 * @since 3.28.0
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String join(Object input,String separator) {
		if(input instanceof String) {
			return (String) input;
		}
		if(input instanceof String[]) {
			input = Arrays.asList((String[])input);
		}
		if(input instanceof Collection) {
			String sep = firstNonNull(separator,",");
			return  (String) ((Collection)input).stream()
					.map(o->getFormatJsonFieldFunction().apply(String.valueOf(o)))
					.reduce((l,r)->String.valueOf(l)+sep+String.valueOf(r))
					.orElse("");
		}
		return String.valueOf(input);
	}
	/**
	 * @param result
	 * @param value
	 * @return 返回输出元素个数
	 */
	@SuppressWarnings("rawtypes")
	private int appendValue(StringBuilder result,Object value){
		if(null == value) {
			result.append("NULL");
			return 1;
		}else if(value.getClass().isArray() && 1 == Array.getLength(value))
		{
			appendValue(result,Array.get(value, 0));
			return 1;
		}else  if (value instanceof boolean[]) 
	    {
	   		appendInBraces(result, Arrays.toString((boolean[])value)); 
	   		return Array.getLength(value);
	    }
	    else if (value instanceof byte[]) 
	    {
	       appendInBraces(result, Arrays.toString((byte[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof short[]) 
	    {
	       appendInBraces(result, Arrays.toString((short[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof int[]) 
	    {
	       appendInBraces(result, Arrays.toString((int[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof long[]) 
	    {
	       appendInBraces(result, Arrays.toString((long[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof float[]) 
	    {
	       appendInBraces(result, Arrays.toString((float[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof double[]) 
	    {
	       appendInBraces(result, Arrays.toString((double[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof char[]) 
	    {
	       appendInBraces(result, Arrays.toString((char[])value));
	       return Array.getLength(value);
	    }
	    else if (value instanceof Object[]) 
	    {
	       result.append('(');
	       int c = 0;
	       for(Object v:(Object[])value) {
	    	   if(c++ > 0) {
	    		   result.append(',');
	    	   }
	    	   appendValue(result,v);
	       }
	       result.append(')');
	       return c;
	    }
	    else if (value instanceof Iterator) 
	    {
	    	result.append('(');
	    	int c = 0;
	    	Iterator itor = (Iterator)value;
	    	while(itor.hasNext()) {
	    		if(c++ > 0) {
	    			result.append(',');
	    		}
	    		appendValue(result,itor.next());
	    	}
	    	result.append(')');
	    	return c;
	    }
	    else if (value instanceof Iterable) 
	    {
	    	return appendValue(result,((Iterable)value).iterator());
	    }
	    else if (value instanceof String) 
	    {
	       result.append("'").append(value).append("'");
	    }
	    else if (value instanceof Date) 
	    {
	    	result.append("'").append(new SimpleDateFormat(timeFormatter).format((Date)value)).append("'");
	    }
	    else 
	    {
	       result.append(value);
	    }
		return 1;
	}
    /**
	 * 对输入字符串中的0,回车符(\r),换行符(\n),反斜杠(\),单引号,双引号,'\032'控制字符进行转义:
	 * <ul>
	 * <li>0转为'\0'</li>
	 * <li>回车符转为'\r'</li>
	 * <li>反斜杠转为'\\'</li>
	 * <li>换行符转为'\n'</li>
	 * <li>单引号转为'\''</li>
	 * <li>双引号转为'\"'</li>
	 * <li>'\032'控制字符转为'\Z'</li>
	 * </ul>
     * @param input the input value
     */
    private static String escape(String input) {
    	if(null ==input) {
    		return null;
    	}
        int stringLength = input.length();

        boolean escaped = false;

        StringBuilder buf = new StringBuilder((int) (input.length() * 1.1));

        for (int i = 0; i < stringLength; ++i) {
            char c = input.charAt(i);

            switch (c) {
                case 0: 
                    buf.append('\\');
                    buf.append('0');
                    escaped = true;
                    break;

                case '\n': 
                    buf.append('\\');
                    buf.append('n');
                    escaped = true;

                    break;

                case '\r':
                    buf.append('\\');
                    buf.append('r');
                    escaped = true;

                    break;

                case '\\':
                    buf.append('\\');
                    buf.append('\\');
                    escaped = true;

                    break;

                case '\'':
                    buf.append('\\');
                    buf.append('\'');
                    escaped = true;

                    break;

                case '"': /* Better safe than sorry */
                    buf.append('\\');
                    buf.append('"');
                    escaped = true;

                    break;

                case '\032': /* This gives problems on Win32 */
                    buf.append('\\');
                    buf.append('Z');
                    escaped = true;

                    break;

                default:
                    buf.append(c);
            }
        }

        return escaped ? buf.toString(): input;
    
    }
    /**
     * @see #escape(String)
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
	private static Object escape(Object input) {
		if (input instanceof String) {
			return escape((String) input);
		} else if (input instanceof Iterator) {
			ArrayList list = new ArrayList();
			Iterator itor = (Iterator) input;
			while (itor.hasNext()) {
				list.add(escape(itor.next()));
			}
			return list;
		} else if (input instanceof Iterable) {
			return escape(((Iterable) input).iterator());
		} else if (null != input) {
			if (input.getClass().isArray()) {
				for (int i = 0, end_i = Array.getLength(input); i < end_i; ++i) {
					Array.set(input, i, escape(Array.get(input, i)));
				}
			}
		}
		return input;
	}
	private void appendInBraces(StringBuilder buf, String s) {
        buf.append('(').append(s.substring(1,s.length()-1)).append(')');
    }
	private String stringOf(Object value, AtomicInteger sizeOut) {
		StringBuilder builder = new StringBuilder();
		int size = appendValue(builder, value);
		if(null != sizeOut) {
		    sizeOut.set(size);
		}
		return builder.toString();
	}
	public String asCond(Object value, boolean not) {
		if(null == value) {
			return "IS " + (not?"NOT ":"") + "NULL";
		}
		AtomicInteger sizeOut = new AtomicInteger(0);
		String string = stringOf(value, sizeOut);
		if(sizeOut.get() > 1) {
			return String.format("%sIN %s", (not?"NOT ":""), string);
		}
		return String.format("%s= %s", (not?"!":""), string);
	}
	/**
	 * 从一个JavaBean或Map中返回字段值,如果字段不存在则返回{@code null}
	 * @param obj JavaBean或Map 实例
	 * @param field
	 * @see PropertyUtilsBean#getProperty(Object, String)
	 */
	private Object valueOf(Object obj,String  field){
		try {
			return propertyUtils.getProperty(obj, field);
		} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
			return null;
		}
	}
	
	/**
	 * 如果变量没有定义则使用默认值定义
	 * @param varname 变量名
	 * @param defaultValue 默认值
	 * @param camelCase 如果变量名不是驼峰命名法格式，是否定义驼峰命名格式的变量
	 * @throws EvalError
	 */
	private void initVarIfNotdefined(String varname,Object defaultValue,boolean camelCase) throws EvalError{
		if(!isNullOrEmpty(varname)) {
			String camelCaseName;
			if(null == interpreter.get(varname)){
				interpreter.set(varname, defaultValue);
			}else if(camelCase && !(camelCaseName = WhereHelpers.toCamelcase(varname)).equals(varname)){
				interpreter.set(camelCaseName, interpreter.get(varname));	
			}
		}
	}
	/**
	 * 判断{@code varname}是否为WhereHelper的引用变量,如果是返回对应的引用变量，
	 * 如果不是返回 {@code null}
	 * @param varname
	 */
	private String isRefVar(String varname){
		if(referenceVariables.contains(varname)){
			return varname;
		}
		String v;
		if(referenceVariables.contains((v = toCamelcase(varname)))){
			return v;
		}
		if(referenceVariables.contains((v = toSnakecase(varname)))){
			return v;
		}
		return null;
	}
	/**
	 * 根据输入的参数对象提供的SQL查询要求的字段参数定义脚本执行变量<br>
	 * SQL查询字段参数可以封装在Java Bean或Map对象，不可为{@code null}<br>
	 * @param params
	 */
	private void initVars(Object params){
		try {			
			if(params instanceof Map){
				Map<?,?> m = (Map<?,?>)params;
				for(Map.Entry<?,?> entry:m.entrySet()){
					if(entry.getKey() instanceof String){
						String refVar = isRefVar((String)entry.getKey());
						if(null != refVar){
							Object value = entry.getValue();
							interpreter.set(checkVarName(refVar), escape(value));
							SimpleLog.log(debuglog,"variable {}={}",refVar,value);
						}
					}
				}
			}else{
				Map<String, PropertyDescriptor> props = BeanPropertyUtils.getProperties(params.getClass(), 3, true);
				for(String field : props.keySet()){
					String refVar = isRefVar(field);
					if(null != refVar){
						Object value = valueOf(params,field);
						interpreter.set(checkVarName(refVar), escape(value));
						SimpleLog.log(debuglog,"variable {}={}",refVar,value);
					}
				}
			}
		} catch (EvalError e) {
			throw new RuntimeException(e);
		}
	}
	private void formatJsonFieldInVar(String varname) throws EvalError {
		Object ov = null == varname ? null : interpreter.get(varname);
		if(ov instanceof String){
			String v = getFormatJsonFieldFunction().apply(String.valueOf(ov));
			if(!ov.equals(v)) {
				interpreter.set(varname, escape(v));
			}
		}
	}
	/**
	 * 执行{@link #where()}前调用
	 * @throws EvalError
	 */
	private void beforeWhere() throws EvalError{
		for(String varname : BUILTIN_VARS){
			initVarIfNotdefined(varname,null,true);
		}
		formatJsonFieldInVar(orderByVar);
		formatJsonFieldInVar(groupByVar);
		initVarIfNotdefined(orderByVar, defaultOrderByColumns, false);
		initVarIfNotdefined(groupByVar, defaultGroupByColumns, false);
	}
	/**
	 * 删除所有定义的变量
	 * @throws EvalError
	 */
	private void unsetVars() throws EvalError{
		for(String varname:Arrays.asList(interpreter.getNameSpace().getVariableNames())){
			if(!KEYWORD_BSH.equals(varname)){
				SimpleLog.log(debuglog,"unset {}",varname);
				interpreter.unset(varname);
			}
		}
	}
	/**
	 * 从{@link RowMetaData}实例或{@link #varTypes}中获取对应的变量类型
	 * @param varname
	 * @return 输入参数为{@code null}或没找到对应的类型返回{@code null}
	 */
	private Class<?> variableTypeOf(String varname){
		if(!isNullOrEmpty(varname)){		
			RowMetaData metaData = RowMetaData.getMetaDataUnchecked(targetClass);
			if(null != metaData){
				Class<?> type = metaData.columnTypeOf(varname);
				if(null != type){
					return type;
				}
				return metaData.columnTypeOf(toCamelcase(varname));
			}
			Class<?> type = varTypes.get(varname);
			if(null != type){
				return type;
			}
			// 自动匹配变量命名格式
			if(isCamelcase(varname)){
				return varTypes.get(toSnakecase(varname));
			}else if (isSnakelcase(varname)) {
				return varTypes.get(toCamelcase(varname));
			}
		}
		return null;
	}
	/**
	 * 主要用于解析从HTTP请求获取的String类型参数<br>
	 * 如果{@code obj}为String类型,尝试解析为或数字(数组),String数组类型,
	 * 解析成功返回解析的实例,否则返回输入参数<br>
	 * 数组类型变量必须解析为List才能在生成代码时被正确识别
	 * @param obj
	 * @param expectType 希望的数据类型,为{@code null}忽略，如果不为{@code null},对于String类型的输入尝试解析为希望类型的对象或数组
	 */
	public static Object guess(Object obj, Class<?> expectType){
		if(obj instanceof String){
			String value = ((String)obj).trim();
			if(!isNullOrEmpty(value)){
			    if(null != expectType){
			        if(value.startsWith("[")){
			        	if(String.class.equals(expectType) && value.endsWith("]") && value.indexOf('"') < 0) {
			        		/** 不加引号的字符串数组特别处理 */
			        		String v = value.replaceAll("^\\[", "").replaceAll("\\]$", "");
			        		return Arrays.stream(v.split("(\\s*[,]\\s*)+")).filter(s->!s.isEmpty()).toArray(String[]::new);			        		
			        	}else {
			        		/** 解析为指定类型的对象数组 */
			        		return JSON.parseObject(value,Array.newInstance(expectType, 0).getClass(),ParserConfig.global,null,0);  
						}
			        } else if(Primitives.unwrap(expectType).isPrimitive() && value.indexOf(',') >= 0){
			            value = "[" + Pattern.compile("(^,|,$)",Pattern.CASE_INSENSITIVE).matcher(value).replaceAll("") + "]";
			            return JSON.parseObject(value,Array.newInstance(expectType, 0).getClass(),ParserConfig.global,null,0);  
			        } else if(String.class.equals(expectType)){
			        	if(value.indexOf(',') >= 0) {
		        			return Arrays.stream(value.split("(\\s*[,]\\s*)+")).filter(s->!s.isEmpty()).toArray(String[]::new);
			        	}else {
							return value;
						}
			        } else {
			            /** 解析为指定类型的对象 */
                        return JSON.parseObject(value,expectType,ParserConfig.global,null,0);
                    }
			    }
			    /**
			     * 以是否以[为前缀判断是否为JSON 数组字符串,如果是则尝试解析为JSONArray
			     */
			    try {
			        if(value.trim().startsWith("[")){
			            return JSON.parseObject(value,JSONArray.class,ParserConfig.global,null,0);
			        }else if(value.indexOf(',') >= 0) {
	        			return Arrays.stream(value.split("(\\s*[,]\\s*)+")).filter(s->!s.isEmpty()).toArray(String[]::new);
		        	}
			    } catch (JSONException e) {
			    }
			}
		}else if(obj != null && null != expectType) {
            checkArgument(obj instanceof Iterable || obj.getClass().isArray() || expectType.isInstance(obj),"obj (%s) is not exprected type %s",obj.getClass().getName(),expectType.getName());
        }
		return obj;
	}
	private static String checkVarName(String varname){
		checkArgument(!KEYWORDS.contains(varname),"'%s' must not be a variable name,because it is protected keyword of WhereHelper or BeanShell",varname);
		return varname;
	}
	
	public static BeanShellWhereBuilder builder(){
		return new BeanShellWhereBuilder();
	}
	
	@Override
	public String toString() {
		return "WhereHelper [" + (timeFormatter != null ? "timeFormatter=" + timeFormatter + ", " : "")
				+ (bshScript != null ? "script=" + bshScript : "") + "]";
	}
}
